"""
患者 CRUD 操作
"""
from typing import List, Optional
from sqlalchemy import select, or_, and_, func
from sqlalchemy.ext.asyncio import AsyncSession

from  crud.base import CRUDBase
from  models.patient import Patient
from  schemas.patient import PatientCreate, PatientUpdate


class CRUDPatient(CRUDBase[Patient, PatientCreate, PatientUpdate]):
    """患者CRUD操作"""
    
    async def get_by_doctor(
        self, 
        db: AsyncSession, 
        *, 
        doctor_id: int, 
        skip: int = 0, 
        limit: int = 100
    ) -> List[Patient]:
        """
        获取指定医生的患者列表
        
        Args:
            db: 数据库会话
            doctor_id: 医生ID
            skip: 跳过的数量
            limit: 返回的最大数量
        
        Returns:
            患者列表
        """
        result = await db.execute(
            select(self.model)
            .where(self.model.doctor_id == doctor_id)
            .where(self.model.status == 1)
            .offset(skip)
            .limit(limit)
        )
        return list(result.scalars().all())
    
    async def search(
        self, 
        db: AsyncSession, 
        *, 
        keyword: str, 
        doctor_id: Optional[int] = None,
        skip: int = 0, 
        limit: int = 100
    ) -> List[Patient]:
        """
        搜索患者（按姓名、手机号、身份证号）
        
        Args:
            db: 数据库会话
            keyword: 搜索关键词
            doctor_id: 医生ID（可选，用于限制搜索范围）
            skip: 跳过的数量
            limit: 返回的最大数量
        
        Returns:
            患者列表
        """
        # 构建查询条件，处理 NULL 值问题
        conditions = []
        
        # 姓名查询（name 不为空）
        conditions.append(self.model.name.ilike(f"%{keyword}%"))
        
        # 手机号查询（只在 phone 不为 NULL 时查询）
        conditions.append(
            and_(
                self.model.phone.isnot(None),
                self.model.phone.ilike(f"%{keyword}%")
            )
        )
        
        # 身份证号查询（只在 id_card 不为 NULL 时查询）
        conditions.append(
            and_(
                self.model.id_card.isnot(None),
                self.model.id_card.ilike(f"%{keyword}%")
            )
        )
        
        query = select(self.model).where(
            or_(*conditions)
        ).where(self.model.status == 1)
        
        print("doctor_id", doctor_id)
        if doctor_id:
            query = query.where(self.model.doctor_id == doctor_id)
        
        query = query.offset(skip).limit(limit)
        result = await db.execute(query)
        return list(result.scalars().all())
    
    async def count_by_doctor(
        self,
        db: AsyncSession,
        *,
        doctor_id: int,
        name: Optional[str] = None,
        id_card: Optional[str] = None,
        phone: Optional[str] = None
    ) -> int:
        """
        计算患者总数（支持条件查询）
        
        Args:
            db: 数据库会话
            doctor_id: 医生ID
            name: 患者姓名（模糊查询）
            id_card: 身份证号（模糊查询）
            phone: 手机号（模糊查询）
        
        Returns:
            患者总数
        """
        query = select(func.count(self.model.id)).where(
            self.model.doctor_id == doctor_id
        ).where(self.model.status == 1)
        
        # 添加模糊查询条件
        if name:
            query = query.where(self.model.name.ilike(f"%{name}%"))
        if id_card:
            query = query.where(
                and_(
                    self.model.id_card.isnot(None),
                    self.model.id_card.ilike(f"%{id_card}%")
                )
            )
        if phone:
            query = query.where(
                and_(
                    self.model.phone.isnot(None),
                    self.model.phone.ilike(f"%{phone}%")
                )
            )
        
        result = await db.execute(query)
        return result.scalar() or 0
    
    async def get_by_id_card(self, db: AsyncSession, *, id_card: str) -> Optional[Patient]:
        """
        根据身份证号获取患者
        
        Args:
            db: 数据库会话
            id_card: 身份证号
        
        Returns:
            患者对象或None
        """
        result = await db.execute(
            select(self.model).where(self.model.id_card == id_card)
        )
        return result.scalar_one_or_none()
    
    async def get_by_id_card_and_phone(
        self,
        db: AsyncSession,
        *,
        id_card: str,
        phone: str
    ) -> Optional[Patient]:
        """
        根据身份证号和手机号获取患者（用于患者登录）
        
        Args:
            db: 数据库会话
            id_card: 身份证号
            phone: 手机号
        
        Returns:
            患者对象或None
        """
        result = await db.execute(
            select(self.model)
            .where(self.model.id_card == id_card)
            .where(self.model.phone == phone)
            .where(self.model.status == 1)
        )
        return result.scalar_one_or_none()


# 创建全局实例
patient = CRUDPatient(Patient)


